﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using Wise.Entities;
using Wise.Util;

namespace NHI.Sync.Tools.Forms
{
    public class Form_Execute_Operator
    {
        public Form_Execute_Operator()
        {

        }

        public Form_Execute_Operator(string name)
        {
            MappdingId = name;
            Name = name;
        }

        public Form_Execute_Operator(表映射 map)
        {
            MappdingId = map.Id.ToString();
            Mapping = map;
            Name = map.ToString();
        }

        public const string INIT_SETTING = "正在检查设置";

        public const string INIT_Connection = "正在初始化连接";

        public const string INIT_SQL = "设置SQL命令";

        public const string INIT_SQL2 = "设置SQL命令2";

        表映射 Mapping = null;

        public string MappdingId { get; set; }

        /// <summary>
        /// 在队列中的索引，用于日志和状态
        /// </summary>
        public int Index
        {
            get
            {
                return Form_Execute.Current.MyOperators.IndexOf(this);
            }
        }

        /// <summary>
        /// 队列的大小，用于日志和状态
        /// </summary>
        public int Total
        {
            get
            {
                return Form_Execute.Current.MyOperators.Count;
            }
        }
        public override string ToString()
        {
            if (Mapping == null)
            {
                return $"{Index}/{Total} {Name}";
            }
            else
            {
                return $"{Index}/{Total} {Mapping.源表名}=>{Mapping.目标表名}";
            }
        }


        void initSQLFiles()
        {
            #region 执行脚本
            if (!string.IsNullOrEmpty(MyDbConfig.Current.数据源.脚本文件夹))
            {
                string dir = FileHelper.ToAbsolute(MyDbConfig.Current.数据源.脚本文件夹);
                var lst = System.IO.Directory.GetFiles(dir, "*.sql", System.IO.SearchOption.AllDirectories)
                    .OrderBy(x => x)
                    .ToList();
                for (int i = 0; i < lst.Count; i++)
                {
                    this.Message = $"执行源数据库 {i}/{lst.Count}";
                    _execSQL(lst[i], MyDbConfig.Current.SourceConnection, MyDbConfig.Current.目标数据库);
                }
            }
            if (!string.IsNullOrEmpty(MyDbConfig.Current.目标数据库.脚本文件夹))
            {
                string dir = FileHelper.ToAbsolute(MyDbConfig.Current.目标数据库.脚本文件夹);
                var lst = System.IO.Directory.GetFiles(dir, "*.sql", System.IO.SearchOption.AllDirectories)
                    .OrderBy(x => x)
                    .ToList();
                for (int i = 0; i < lst.Count; i++)
                {
                    this.Message = $"执行目标数据库 {i}/{lst.Count}";
                    _execSQL(lst[i], MyDbConfig.Current.TargetConnection, MyDbConfig.Current.数据源);
                }
            }
            #endregion

        }


        void initSQLFiles2()
        {
            #region 执行脚本
            if (!string.IsNullOrEmpty(MyDbConfig.Current.数据源.执行完脚本文件夹))
            {
                string dir = FileHelper.ToAbsolute(MyDbConfig.Current.数据源.执行完脚本文件夹);
                var lst = System.IO.Directory.GetFiles(dir, "*.sql", System.IO.SearchOption.AllDirectories)
                    .OrderBy(x => x)
                    .ToList();
                for (int i = 0; i < lst.Count; i++)
                {
                    this.Message = $"执行源数据库 {i}/{lst.Count}";
                    _execSQL(lst[i], MyDbConfig.Current.SourceConnection, MyDbConfig.Current.目标数据库);
                }
            }
            if (!string.IsNullOrEmpty(MyDbConfig.Current.目标数据库.执行完脚本文件夹))
            {
                string dir = FileHelper.ToAbsolute(MyDbConfig.Current.目标数据库.执行完脚本文件夹);
                var lst = System.IO.Directory.GetFiles(dir, "*.sql", System.IO.SearchOption.AllDirectories)
                    .OrderBy(x => x)
                    .ToList();
                for (int i = 0; i < lst.Count; i++)
                {
                    this.Message = $"执行目标数据库 {i}/{lst.Count}";
                    _execSQL(lst[i], MyDbConfig.Current.TargetConnection, MyDbConfig.Current.数据源);
                }
            }
            #endregion

        }

        void _execSQL(string sqlFile, IDbConnection con, 数据库配置 db)
        {
            Queue<string> alltext = new Queue<string>(System.IO.File.ReadAllLines(sqlFile));
            List<String> builder = new List<string>();
            while (alltext.Count > 0)
            {
                string line = alltext.Dequeue().Trim();
                if (string.Equals("Go", line, StringComparison.CurrentCultureIgnoreCase))
                {
                    string sql = String.Join(Environment.NewLine, builder);
                    builder.Clear();
                    if (string.IsNullOrEmpty(sql))
                        continue;
                    Regex regDB = new Regex("\\[\\s*数据源\\s*\\]", RegexOptions.IgnoreCase);
                    sql = regDB.Replace(sql, $"[{db.数据库名称}]");
                    using (IDbCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandTimeout = 20000;
                        cmd.CommandText = sql;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception exp)
                        {
                            throw new Exception(sql, exp);
                        }
                    }
                }
                else
                {
                    builder.Add(line);
                }
            }
            {
                string sql = String.Join(Environment.NewLine, builder);
                Regex regDB = new Regex("\\[\\s*数据源\\s*\\]", RegexOptions.IgnoreCase);
                sql = regDB.Replace(sql, $"[{db.数据库名称}]");
                builder.Clear();
                if (string.IsNullOrEmpty(sql))
                    return;
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandTimeout = 20000;
                    cmd.CommandText = sql;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception exp)
                    {
                        throw new Exception(sql, exp);
                    }
                }
            }

        }
        void _execCommand(List<String> listSQL, string command)
        {
            #region 
            for (int i = 0; i < listSQL.Count; i += 表结构._PAGE_SIZE)
            {
                this.Message = $"{i}/{listSQL.Count}  正在{command} [{Mapping.目标表名}]的数据";
                string CommandText = string.Join(Environment.NewLine,
                                      listSQL.Skip(i).Take(表结构._PAGE_SIZE)
                                       );
                try
                {

                    using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                    {
                        cmd.CommandText = CommandText;
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            DataSet set = DbHelper.DataReaderToDataSet(reader);
                            switch (command)
                            {
                                case "新增":
                                    MyLogger.Current._新增(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    InsertCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                                case "删除":
                                    MyLogger.Current._删除(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    DeletedCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                                case "变更":
                                    MyLogger.Current._覆盖(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    break;
                                case "更新":
                                    MyLogger.Current._覆盖(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    UpdateCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
                catch (SqlException exp)
                {
                    Warning++;
                    MainWindow.Current.ShowError(exp.Message);
                    MyLogger.Current._格式错误(Mapping, CommandText, exp);
                }
                catch (Exception exp)
                {
                    Error++;
                    MainWindow.Current.ShowError(exp.Message);
                    MyLogger.Current._程序异常(new Exception($"映射：{Mapping.ToString()}{Environment.NewLine}SQL：{CommandText}", exp));
                }
            }
            #endregion
        }

        void _execCommand(Dictionary<String, string> listSQL, string command)
        {
            List<String> keys = listSQL.Keys.ToList();
            #region 
            for (int i = 0; i < listSQL.Count; i += 表结构._PAGE_SIZE)
            {
                this.Message = $"{i}/{listSQL.Count}  正在{command} [{Mapping.目标表名}]的数据";
                string CommandText =
                                       string.Join(Environment.NewLine,
                                       keys.Skip(i).Take(表结构._PAGE_SIZE)
                                       .Select(x => listSQL[x])
                                       );
                try
                {

                    using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
                    {
                        cmd.CommandText = CommandText;
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            DataSet set = DbHelper.DataReaderToDataSet(reader);
                            switch (command)
                            {
                                case "新增":
                                    MyLogger.Current._新增(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    InsertCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                                case "删除":
                                    MyLogger.Current._删除(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    DeletedCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                                case "更新":
                                default:
                                    MyLogger.Current._覆盖(Mapping, set.Tables.Cast<DataTable>().SelectMany(t => t.Rows.Cast<DataRow>().Select(x => x[0].ToString())).ToList());
                                    UpdateCount += set.Tables.Cast<DataTable>().Select(t => t.Rows.Count).Sum();
                                    break;
                            }
                        }
                    }
                }
                catch (SqlException exp)
                {
                    Warning++;
                    MainWindow.Current.ShowError(exp.Message);
                    MyLogger.Current._格式错误(Mapping, CommandText, exp);
                }
                catch(ThreadAbortException)
                {

                }
                catch (Exception exp)
                {
                    Error++;
                    MainWindow.Current.ShowError(exp.Message);
                    MyLogger.Current._程序异常(new Exception($"映射：{Mapping.ToString()}{Environment.NewLine}SQL：{CommandText}", exp));
                }
            }
            #endregion
        }

        public void Run()
        {
            this.Status = Form_Execute_StatusType.运行中;
            switch (Name)
            {
                case INIT_SETTING:
                    #region INIT_SETTING
                    {
                        if (MyDbConfig.Current.表映射.Count == 0)
                        {
                            //Cancel无效，因此只能抛出异常
                            throw new Exception("设置错误，无任何表映射！");
                        }
                        else
                        {
                            var listNewOpts = MyDbConfig.Current.表映射.Where(x => x != null && !string.IsNullOrEmpty(x.源表名))
                                .Select(x => new Form_Execute_Operator(x))
                                .ToList();
                            lock (Form_Execute.Current.MyOperators)
                            {
                                Form_Execute.Current.MyOperators.AddRange(listNewOpts);
                            }
                            Form_Execute.Current.MyOperators.Add(new Forms.Form_Execute_Operator(INIT_SQL2));
                            this.Status = Form_Execute_StatusType.运行成功;
                            this.Message = $"共计{listNewOpts.Count}张表";
                            Form_Execute.Current.MyStatus.Total = Form_Execute.Current.MyOperators.Count;
                            Form_Execute.Current.MyStatus.Successed = 0;
                            Form_Execute.Current.MyStatus.Error = 0;
                            Form_Execute.Current.MyStatus.Warning = 0;
                        }
                        Form_Execute.Current.MyStatus.Successed++;
                        this.Status = Form_Execute_StatusType.运行成功;
                    }
                    #endregion
                    break;
                case INIT_Connection:
                    #region INIT_Connection
                    {
                        MyDbConfig.Current.CloseConnections();
                        try
                        {
                            MyDbConfig.Current.SourceConnection = new SqlConnection(MyDbConfig.Current.数据源.GetConnectionString());
                            MyDbConfig.Current.CreateSourceConnection = () => new SqlConnection(MyDbConfig.Current.数据源.GetConnectionString());
                            MyDbConfig.Current.SourceConnection.Open();
                        }
                        catch (Exception exp)
                        {
                            throw new Exception("尝试打开源数据库时出错", exp);
                        }
                        try
                        {
                            MyDbConfig.Current.TargetConnection = new SqlConnection(MyDbConfig.Current.目标数据库.GetConnectionString());
                            MyDbConfig.Current.CreateTargetConnection = () => new SqlConnection(MyDbConfig.Current.目标数据库.GetConnectionString());
                            MyDbConfig.Current.TargetConnection.Open();
                        }
                        catch (Exception exp)
                        {
                            throw new Exception("尝试打开目标数据库时出错", exp);
                        }
                        Form_Execute.Current.MyStatus.Successed++;
                        this.Status = Form_Execute_StatusType.运行成功;
                        this.Message = "连接成功";
                    }
                    #endregion
                    break;
                case INIT_SQL:
                    #region INIT_SQL
                    {
                        List<Form_Execute_Operator> ops = Form_Execute.Current.MyOperators
                        .Where(x => x.Mapping != null)
                        .ToList();
                        initSQLFiles();
                        for (int i = 0; i < ops.Count; i++)
                        {
                            #region 逐操作处理SQL
                            var that = ops[i];
                            this.Message = $"正在初始化第{i + 1}个映射关系[{that.Mapping.ToString()}]，共计{ops.Count}个！";
                            bool isTableDefineEmpty = false;
                            if (string.IsNullOrWhiteSpace(that.Mapping.目标表名))
                            {
                                isTableDefineEmpty = true;
                                MyLogger.Current._警告($"表{that.Mapping.源表名} 没有配置目标表名，已自动使用{that.Mapping.源表名}作为目标表", INIT_SQL);
                                Warning++;
                                that.Mapping.目标表名 = that.Mapping.源表名;
                            }
                            #region 初始化SQL语句
                            {
                                that.Mapping._源表 = 表映射.Create表结构(that.Mapping.源表名, MyDbConfig.Current.SourceConnection);
                                that.Mapping._目标表 = 表映射.Create表结构(that.Mapping.目标表名, MyDbConfig.Current.TargetConnection);
                                if (!that.Mapping._目标表._Exists && isTableDefineEmpty)
                                {
                                    that.Mapping.目标表名 = String.Concat(MyDbConfig.Current.新增表前缀, that.Mapping.源表名);
                                    MyLogger.Current._警告($"目标表{that.Mapping.源表名} 不存在，已自动使用{that.Mapping.目标表名}作为目标表", INIT_SQL);
                                    Warning++;
                                    that.Mapping._目标表 = 表映射.Create表结构(that.Mapping.目标表名, MyDbConfig.Current.TargetConnection);
                                }
                            }
                            #endregion

                            //ThreadPool.QueueUserWorkItem((par) =>
                            //{
                            //    Tuple<Form_Execute_Operator, Form_Execute_Operator> tp = (Tuple<Form_Execute_Operator, Form_Execute_Operator>)par;
                            //    tp.Item2.Mapping.InitSQL(tp.Item1);
                            //},new Tuple<Form_Execute_Operator, Form_Execute_Operator>(this,that));
                            //由于前后存在顺序，因此无法用多线程提速
                            //that.Mapping.InitSQL(this);
                            #endregion
                        }
                        this.Message = $"共计初始化完成{ops.Count}个映射关系!";
                        this.Status = Form_Execute_StatusType.运行成功;

                        Form_Execute.Current.MyStatus.Successed++;
                    }
                    #endregion
                    break;
                case INIT_SQL2:
                    initSQLFiles2();
                    this.Status = Form_Execute_StatusType.运行成功;
                    break;
                default:
                    #region default
                    {
                        if (Mapping == null)
                        {
                            throw new Exception($"没有映射关系[{Name}]");
                        }
                        else
                        {
                            if (Mapping.测试)
                            {
                                //debugger;
                            }
                            this.Status = Form_Execute_StatusType.运行中;
                            Mapping.InitSQL(this);
                            {
                                Dictionary<String, string> listSQL = Mapping._SQL.Item3;
                                _execCommand(listSQL, "删除");
                            }
                            {
                                Dictionary<String, string> listSQL = Mapping._SQL.Item4;
                                _execCommand(listSQL, "更新");
                            }
                            {
                                Dictionary<String, string> listSQL = Mapping._SQL.Item2;
                                _execCommand(listSQL, "新增");
                            }
                            int lastCount = 0, tick = 0;
                            while (Mapping._Uploading_Files_Done < Mapping._Uploading_Files_Total
                                //10分钟过后，已上传数量依然没有变化，则认为已上传
                                && tick < 600)
                            {
                                if (Mapping._Uploading_Files_Done != lastCount)
                                {
                                    lastCount = Mapping._Uploading_Files_Done;
                                    tick = 0;
                                }
                                this.Message = $"等待上传文件，进度:{Mapping._Uploading_Files_Done}/{Mapping._Uploading_Files_Total}";
                                Thread.Sleep(1000);
                                tick++;
                            }
                            {
                                List<string> listSQL = Mapping._SQL.Item5.ToList();
                                _execCommand(listSQL, "变更");
                            }
                            this.Message = $"新增{InsertCount}条 删除{DeletedCount}条 更新{UpdateCount}条";
                            this.Status = Form_Execute_StatusType.运行成功;
                            if (Error > 0 || Warning > 0)
                            {
                                Form_Execute.Current.MyStatus.Warning++;
                            }
                            else
                            {
                                Form_Execute.Current.MyStatus.Successed++;
                            }
                        }
                    }
                    #endregion
                    break;
            }
        }

        public Form_Execute_StatusType Status { get; set; } = Form_Execute_StatusType.等待运行;

        public String StatusText { get { return Status.ToString(); } }

        public string Message { get; set; }

        public string Name { get; set; }

        public int Error { get; set; }

        public int Warning { get; set; }

        public int Success { get; set; }

        /// <summary>
        /// 删除行数
        /// </summary>
        public int DeletedCount { get; set; }

        /// <summary>
        /// 添加行数
        /// </summary>
        public int InsertCount { get; set; }

        /// <summary>
        /// 更新行数
        /// </summary>
        public int UpdateCount { get; set; }

        public string ResultText
        {
            get
            {
                return $"{Success}成功 {Warning}警告 {Error}错误";
            }
        }
    }

}
